using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Transactions;

namespace mfe.lib.data
{
    public class Database
    {
        //private ConnectionString connectionString;
        private DbProviderFactory dbProviderFactory;
        private DbConnection dbConnection;
        private DbTransaction dbTransaction;
        private char parameterToken = '@';

        /// <summary>
        /// <para>Gets the DbProviderFactory used by the database instance.</para>
        /// </summary>
        /// <seealso cref="DbProviderFactory"/>
        public DbProviderFactory DbProviderFactory
        {
            get { return this.dbProviderFactory; }
            set { dbProviderFactory = value; }
        }

        public DbConnection DbConnection
        {
            get { return dbConnection; }
            set { dbConnection = value; }
        }

        public DbTransaction DbTransaction
        {
            get { return dbTransaction; }
            set { dbTransaction = value; }
        }

        public char ParameterToken
        {
            get { return parameterToken; }
            set { parameterToken = value; }
        }

        public Database()
        {
        }


        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the in parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
        /// <remarks>
        /// <para>This version of the method is used when you can have the same parameter object multiple times with different values.</para>
        /// </remarks>        
        public void AddInParameter(DbCommand command, string name, DbType dbType)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The commmand to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
        /// <param name="value"><para>The value of the parameter.</para></param>      
        public void AddInParameter(DbCommand command, string name, DbType dbType, object value)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the value.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        public void AddInParameter(DbCommand command, string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
        {
            AddParameter(command, name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
        }

        /*
        public virtual DataSet ExecuteDataSet(DbCommand command)
        {
            DataSet dataSet = new DataSet();
            dataSet.Locale = CultureInfo.InvariantCulture;
            LoadDataSet(command, dataSet, "Table");
            return dataSet;
        }

        private void DoLoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
        {
            if (tableNames == null) throw new ArgumentNullException("tableNames");
            if (tableNames.Length == 0)
            {
                throw new ArgumentException(Resources.ExceptionTableNameArrayEmpty, "tableNames");
            }
            for (int i = 0; i < tableNames.Length; i++)
            {
                if (string.IsNullOrEmpty(tableNames[i])) throw new ArgumentException(Resources.ExceptionNullOrEmptyString, string.Concat("tableNames[", i, "]"));
            }

            using (DbDataAdapter adapter = GetDataAdapter(UpdateBehavior.Standard))
            {
                ((IDbDataAdapter)adapter).SelectCommand = command;

                try
                {
                    DateTime startTime = DateTime.Now;
                    string systemCreatedTableNameRoot = "Table";
                    for (int i = 0; i < tableNames.Length; i++)
                    {
                        string systemCreatedTableName = (i == 0)
                             ? systemCreatedTableNameRoot
                             : systemCreatedTableNameRoot + i;

                        adapter.TableMappings.Add(systemCreatedTableName, tableNames[i]);
                    }

                    adapter.Fill(dataSet);
                    instrumentationProvider.FireCommandExecutedEvent(startTime);
                }
                catch (Exception e)
                {
                    instrumentationProvider.FireCommandFailedEvent(command.CommandText, ConnectionStringNoCredentials, e);
                    throw;
                }
            }
        }
        */

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>
        /// <param name="size"><para>The maximum size of the data within the column.</para></param>
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
        /// <param name="nullable"><para>Avalue indicating whether the parameter accepts <see langword="null"/> (<b>Nothing</b> in Visual Basic) values.</para></param>
        /// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
        /// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>       
        public virtual void AddParameter(DbCommand command, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            DbParameter parameter = CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
            command.Parameters.Add(parameter);
        }

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object to the command.</para>
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>        
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>    
        public void AddParameter(DbCommand command, string name, DbType dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            AddParameter(command, name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
        }


        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object.</para>
        /// </summary>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <param name="dbType"><para>One of the <see cref="DbType"/> values.</para></param>
        /// <param name="size"><para>The maximum size of the data within the column.</para></param>
        /// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
        /// <param name="nullable"><para>Avalue indicating whether the parameter accepts <see langword="null"/> (<b>Nothing</b> in Visual Basic) values.</para></param>
        /// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
        /// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
        /// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
        /// <param name="value"><para>The value of the parameter.</para></param>  
        /// <returns>A newly created <see cref="DbParameter"/> fully initialized with given parameters.</returns>
        protected DbParameter CreateParameter(string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            DbParameter param = CreateParameter(name);
            ConfigureParameter(param, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
            return param;
        }

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object.</para>
        /// </summary>
        /// <param name="name"><para>The name of the parameter.</para></param>
        /// <returns><para>An unconfigured parameter.</para></returns>
        protected DbParameter CreateParameter(string name)
        {
            DbParameter param = dbProviderFactory.CreateParameter();
            param.ParameterName = BuildParameterName(name);

            return param;
        }

        /// <summary>
        /// Builds a value parameter name for the current database.
        /// </summary>
        /// <param name="name">The name of the parameter.</param>
        /// <returns>A correctly formated parameter name.</returns>
        public virtual string BuildParameterName(string name)
        {
            return parameterToken + name;
        }

        protected virtual void ConfigureParameter(DbParameter param, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            param.DbType = dbType;
            param.Size = size;
            param.Value = (value == null) ? DBNull.Value : value;
            param.Direction = direction;
            param.IsNullable = nullable;
            param.SourceColumn = sourceColumn;
            param.SourceVersion = sourceVersion;
        }

        public DbCommand GetSqlStringCommand(string sql)
        {
            System.Configuration.ConnectionStringSettingsCollection connstrs = System.Configuration.ConfigurationManager.ConnectionStrings;
            string defaultDatabase = System.Configuration.ConfigurationManager.AppSettings["defaultDatabase"];
            string variant = connstrs[defaultDatabase].ProviderName;


            DbProviderFactory factory = DbProviderFactories.GetFactory(variant);
            DbCommand cmd = factory.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;

            return cmd;
        }

        public virtual void LoadDataSet(DbCommand command, DataSet dataSet, string tableName)
        {
            //if (DbConnection == null)
            //{
            //    DbConnection = dbProviderFactory.CreateConnection();
            //    DbConnection.Open();
            //}

            if (DbConnection.State != ConnectionState.Open)
                DbConnection.Open();

            PrepareCommand(command);
            command.Connection = DbConnection;
            DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dataSet, tableName);
            DbConnection.Close();
            //DbConnection.Dispose();
        }

        public virtual void LoadDataTable(DbCommand command, DataTable dataTable)
        {
            //if (DbConnection == null)
            //{
            //    DbConnection = dbProviderFactory.CreateConnection();
            //    DbConnection.Open();
            //}

            if (DbConnection.State != ConnectionState.Open)
                DbConnection.Open();

            PrepareCommand(command);
            command.Connection = DbConnection;
            DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dataTable);
            DbConnection.Close();
            //DbConnection.Dispose();
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns an <see cref="IDataReader"></see> through which the result can be read.
        /// It is the responsibility of the caller to close the connection and reader when finished.</para>
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <returns>
        /// <para>An <see cref="IDataReader"/> object.</para>
        /// </returns>        
        public virtual IDataReader ExecuteReader(DbCommand command)
        {
            //ConnectionWrapper wrapper = GetOpenConnection(false);

            try
            {
                if (DbConnection.State != ConnectionState.Open)
                    DbConnection.Open();

                PrepareCommand(command);
                command.Connection = DbConnection;

                //
                // JS-L: I moved the PrepareCommand inside the try because it can fail.
                //
                //PrepareCommand(command, wrapper.Connection);

                //
                // If there is a current transaction, we'll be using a shared connection, so we don't
                // want to close the connection when we're done with the reader.
                //
                if (Transaction.Current != null)
                    return command.ExecuteReader(CommandBehavior.Default);
                else
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                dbConnection.Close();
                dbConnection.Dispose();
                throw;
            }
        }


        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns the number of rows affected.</para>
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>       
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public virtual int ExecuteNonQuery(DbCommand command)
        {
            try
            {
                if (DbConnection.State != ConnectionState.Open)
                    DbConnection.Open();

                PrepareCommand(command);
                command.Connection = DbConnection;

                int rowsAffected = command.ExecuteNonQuery();

                return rowsAffected;
            }
            catch
            {
                dbConnection.Close();
                dbConnection.Dispose();
                throw;
            }
            finally
            {
                dbConnection.Close();
                dbConnection.Dispose();
            }
        }


        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.</para>
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <returns>
        /// <para>The first column of the first row in the result set.</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public virtual object ExecuteScalar(DbCommand command)
        {
            if (command == null) throw new ArgumentNullException("command");

            try
            {
                if (DbConnection.State != ConnectionState.Open)
                    DbConnection.Open();

                PrepareCommand(command);
                command.Connection = DbConnection;

                object returnValue = command.ExecuteScalar();

                return returnValue;
            }
            catch
            {
                dbConnection.Close();
                dbConnection.Dispose();
                throw;
            }
            finally
            {
                //close
                dbConnection.Close();
                dbConnection.Dispose();
            }
        }

        public static bool IsDirty(DataSet ds)
        {
            bool isDirty = false;
            foreach (DataTable dt in ds.Tables)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr.RowState != DataRowState.Unchanged)
                    {
                        isDirty = true;
                        break;
                    }
                }
            }
            return isDirty;
        }

        protected void PrepareCommand(DbCommand command)
        {
            if (command == null) throw new ArgumentNullException("command");
            //if (connection == null) throw new ArgumentNullException("connection");

            //command.Connection = connection;
            PrepareCommandText(command);
        }

        /// <summary>
        /// Prepares the command text base on the factory provider. For example,
        /// if the sql statement is "select * from tableA where ID=@ID", and
        /// if the provider is MySql.Data.MySqlClient, modify the sql statement
        /// to "select * from tableA where ID=?ID". Lib.dll assume the default
        /// is MSSQL Server.
        /// </summary>
        /// <param name="command">The command.</param>
        private void PrepareCommandText(DbCommand command)
        {
            Regex reg = new Regex("@");
            command.CommandText = reg.Replace(command.CommandText, parameterToken.ToString());
        }
    }
}
